# Prepare UT returns
# Note: clean and rearrange 2020 UT returns
# By Dom Valentino and Chris Kenny
# libs --------------------------------------------------------------------
library(tidyverse) # select(), filter(), mutate(), etc.
library(haven) # read_dta()
library(readxl) # read_excel()

# data --------------------------------------------------------------------
returns_pres <- read_excel('../data/returns_ut/returns_ut_pres.xlsx')
returns_gov <- read.csv('../data/returns_ut/returns_ut_gov.csv')
turnout <- read_excel('../data/returns_ut/utah_turnout_by_hand.xlsx')
cvap <- read_dta("../data/modified data/county_cvap.dta")

# clean
final_ut <- returns_pres %>% left_join(., returns_gov, by = "county") %>% 
  transmute(county = county, 
            fips = NA_real_,
            treat = 1,
            dem_share_pres = as.numeric(dem_pres) / (as.numeric(dem_pres) + as.numeric(rep_pres)), 
            dem_share_sen = NA_real_,
            dem_share_gov = as.numeric(dem_gov) / (as.numeric(dem_gov) + as.numeric(rep_gov)),
            year = 2020) %>% 
  left_join(., turnout, by = "county") %>% # merge turnout
  left_join(., cvap[cvap$year == 2020 & cvap$state == "UT", ], by = "county") %>% 
  mutate(year = year.x, turnout_share = ballots_cast / cvap_approx) %>% 
  select(-year.y, -year.x, -`Total Active Registered`, -`Percent Turnout`)

write_csv(final_ut, "../data/analysis/analysis_ut.csv")
